'''
Author: duliang thinktanker@163.com
Date: 2025-07-10 19:37:16
LastEditors: duliang thinktanker@163.com
LastEditTime: 2025-08-22 09:35:59
FilePath: 
Description: 
'''
# from pyzbar import pyzbar
# from PIL import Image
import re
import cv2
import sqlite3
import time
from threading import Thread
# 自定义模块
from diancan import get_start_and_end_of_day
import wechatsubscribe
from ocr import ocr
# 初始化检测器（替换为你的文件路径）
detector = cv2.wechat_qrcode_WeChatQRCode(
    "./config/wechatORCode/detect.prototxt",
    "./config/wechatORCode/detect.caffemodel",
    "./config/wechatORCode/sr.prototxt", "./config/wechatORCode/sr.caffemodel")

admin_openid = "oPQDE5KA-SXhlayCSSaH6VoB31Pk"


def scan_qrcode(image_path):
    # 读取图像
    img = cv2.imread(image_path)

    # 检测和识别二维码
    texts, points = detector.detectAndDecode(img)
    results = []
    if texts:
        print(f"找到 {len(texts)} 个二维码:")
        for i, (text, pts) in enumerate(zip(texts, points)):
            # 打印结果
            # print(f"二维码 {i+1}: {text}")
            results.append({"data": text, "type": "QRCODE"})
            # # 可视化二维码位置
            # pts = pts.astype(int)
            # cv2.polylines(img, [pts], True, (0, 255, 0), 2)
            # cv2.putText(img, f"QR {i+1}", tuple(pts[0]),
            #             cv2.FONT_HERSHEY_SIMPLEX, 0.8, (0, 0, 255), 2)

        # # 显示结果
        # cv2.imshow("WeChat QR Code", img)
        # cv2.waitKey(0)
        # cv2.destroyAllWindows()
    else:
        print("未检测到二维码")
    return results


def save(data, user):
    '''
    保存二维码图片路径和数据到数据库
    '''
    # checked = 0
    # if user == "oPQDE5KA-SXhlayCSSaH6VoB31Pk":
    #     checked = 1
    # else:
    #     checked = 0
    checked = 0
    checked_return_value = 0
    conn = sqlite3.connect('./data/fapiao.db')
    cursor = conn.cursor()
    # 创建表（如果不存在）
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS fapiao (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            image_path TEXT,
            data TEXT,
            time INTEGER,
            user TEXT
        )
    ''')
    # 判断data是否已经存在
    cursor.execute('SELECT time,user FROM fapiao WHERE data = ?', (data, ))
    result = cursor.fetchone()
    if result is None:
        now_time = int(time.time())
        # 插入数据
        if len(data.split(',')) > 5:
            cursor.execute(
                'INSERT INTO fapiao (data,time,user,checked,maketime) VALUES (?, ?,?,?,?)',
                (data, now_time, user, checked, data.split(',')[5]))
        else:
            cursor.execute(
                'INSERT INTO fapiao (data,time,user,checked) VALUES (?, ?,?,?)',
                (data, now_time, user, checked))
        conn.commit()
        conn.close()
        realname = getRealnameByopenid(user)[1]  # 获取用户名
        th = Thread(
            target=wechatsubscribe.send_notification,
            args=(admin_openid,
                  "信息", (realname,
                         time.strftime("%Y-%m-%d %H:%M:%S",
                                       time.localtime(now_time))),
                  f"pages/tools/tools?query={realname}@{now_time}@{data}"),
            daemon=True)
        th.start()
        return True, result
    else:
        print(f"'{data}' 已存在，更新")
        others = []
        cursor.execute(
            'SELECT others,otherstime,checked FROM fapiao WHERE data = ?',
            (data, ))
        user_others_data = cursor.fetchone()
        if user_others_data:
            # user_old = user_others_data[0]
            others = eval(user_others_data[0]) if user_others_data[0] else []
            otherstime = eval(
                user_others_data[1]) if user_others_data[1] else []
            # if user not in others:
            others.append(user)
            otherstime.append(int(time.time()))
            print(f"添加用户 {user} 到others列表")
            # "desc": "发送人{{thing3.DATA}}发送时间{{time2.DATA}}"

            # else:
            #     print(f"用户 {user} 已经在others列表中，跳过保存")
        checked = user_others_data[2] if user_others_data else 0
        if checked == 1:
            checked_return_value = 1
        if user == admin_openid and checked == 0:
            checked_return_value = 1
        cursor.execute(
            'UPDATE fapiao SET checked = ?,others = ?,otherstime = ? WHERE data = ?',
            (checked_return_value, str(others), str(otherstime), data))
        conn.commit()
        conn.close()
        if checked == 0:
            subID, uptime = getOpenidByFapiaoData(data=data)
            uptime = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(uptime))
            th = Thread(
                target=wechatsubscribe.send_notification,
                args=(subID, '审批', (
                    "已审批", uptime, data.split(',')[4],
                    time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()), "通过",
                    f"pages/tools/tools?query={uptime}@{data.split(',')[4]}")),
                daemon=True)
            th.start()
        for index, openid in enumerate(others):
            others[index] = getRealnameByopenid(openid)[1]
        return False, (result[0], getRealnameByopenid(result[1])[1],
                       checked_return_value, others, otherstime)


def updateFapiaoDetail(data, user, filepath):
    '''
    更新发票详情调用ocr识别
    '''
    conn = sqlite3.connect('./data/fapiao.db')
    cursor = conn.cursor()
    cursor.execute('SELECT id FROM fapiao WHERE data = ?', (data, ))
    res = cursor.fetchone()
    if res:
        try:
            result, _ = ocr(filepath, user)
            if result:
                if result[0]:
                    cursor.execute(
                        'UPDATE fapiao SET information = ? WHERE id = ?',
                        (str(result[0]), res[0]))
                    conn.commit()
                    print("插入发票详情成功")
        except Exception as e:
            print(f"Error in OCR processing: {e}")
    conn.close()


def getRealnameByopenid(openid):
    '''
    由openid获取用户名
    '''
    conn = sqlite3.connect('./data/users.db')
    cursor = conn.cursor()
    cursor.execute('SELECT id,username FROM users WHERE openid = ?',
                   (openid, ))
    # cursor.fetchone()
    result = cursor.fetchone()
    conn.close()
    return result  # 返回用户名


def getFapiaoInfoByData(data):
    '''
    通过 data 获取发票 information
    '''
    # 将data中的汉字替换为%
    data = re.sub(r'[\u4e00-\u9fa5]', '%', data)
    # 将\n和:替换为空格
    data = re.sub(r'[\n：]', '%', data)
    print(data)
    conn = sqlite3.connect('./data/fapiao.db')
    cursor = conn.cursor()
    cursor.execute('SELECT information FROM fapiao WHERE data LIKE ?',
                   (data, ))
    result = cursor.fetchone()
    conn.close()
    if result:
        return result[0]
    else:
        return None


def getFapiaoByTime(start_time, end_time):
    '''
    根据时间范围获取发票数据，并替换openid为username
    '''
    # 获取发票数据
    start_time = get_start_and_end_of_day(start_time)[0]
    end_time = get_start_and_end_of_day(end_time)[1]
    conn = sqlite3.connect('./data/fapiao.db')
    cursor = conn.cursor()
    cursor.execute(
        'SELECT data,user,time,checked,maketime FROM fapiao WHERE time >= ? AND time <= ? order BY time DESC',
        (start_time, end_time))
    results = cursor.fetchall()
    conn.close()

    if not results:
        return []

    # 提取唯一的openid列表
    openid_list = list(set(result[1] for result in results))

    # 查询对应的username
    username_map = {}
    if openid_list:
        placeholders = ','.join(['?'] * len(openid_list))
        query = f'SELECT openid, username FROM users WHERE openid IN ({placeholders})'

        conn = sqlite3.connect('./data/users.db')
        cursor = conn.cursor()
        cursor.execute(query, openid_list)

        # 构建 {openid: username} 映射
        username_map = {
            openid: username
            for openid, username in cursor.fetchall()
        }
        conn.close()

    # 替换openid为username，处理可能缺失的映射情况
    updated_results = []
    for data, openid, time, checked, maketime in results:
        username = username_map.get(openid,
                                    "未知用户")  # 如果找不到对应username，默认使用"未知用户"
        updated_results.append((data, username, time, checked, maketime))

    return updated_results


def getAllnames():
    '''
    获取所有用户名
    '''
    # 获取发票数据
    conn = sqlite3.connect('./data/fapiao.db')
    cursor = conn.cursor()
    cursor.execute('SELECT user FROM fapiao group by user order by time desc')
    results = cursor.fetchall()
    conn.close()

    if not results:
        return []

    # 提取唯一的openid列表
    openid_list = list(set(result[0] for result in results))
    # print(openid_list)
    # 查询对应的username
    username_map = {}
    if openid_list:
        placeholders = ','.join(['?'] * len(openid_list))
        query = f'SELECT openid, username FROM users WHERE openid IN ({placeholders})'

        conn = sqlite3.connect('./data/users.db')
        cursor = conn.cursor()
        cursor.execute(query, openid_list)

        # 构建 {openid: username} 映射
        username_map = {
            openid: username
            for openid, username in cursor.fetchall()
        }
        conn.close()
    # print(username_map)
    # 替换openid为username，处理可能缺失的映射情况
    updated_results = []
    for openid in results:
        # print(openid)
        username = username_map.get(openid[0],
                                    "未知用户")  # 如果找不到对应username，默认使用"未知用户"
        updated_results.append(username)

    return updated_results


def getOpenidByName(name):
    '''
    根据name获取openid
    '''
    conn = sqlite3.connect('./data/users.db')
    cursor = conn.cursor()
    cursor.execute('SELECT openid FROM users WHERE username = ?', (name, ))
    result = cursor.fetchone()
    conn.close()
    if result:
        return result[0]
    else:
        return None


def getFapiaoByOpenid(openid):
    '''
    根据openid获取发票数据
    '''
    conn = sqlite3.connect('./data/fapiao.db')
    cursor = conn.cursor()
    cursor.execute(
        'SELECT data, time,checked,others,otherstime FROM fapiao WHERE user = ? ORDER BY id DESC',
        (openid, ))
    results = cursor.fetchall()
    conn.close()

    if not results:
        return []

    # 返回格式为 (data, time)
    return [(data, time, checked, others, otherstime)
            for data, time, checked, others, otherstime in results]


def getFapiaoByName(name):
    '''
    根据用户名获取发票数据
    '''
    conn = sqlite3.connect('./data/users.db')
    cursor = conn.cursor()
    cursor.execute('SELECT openid FROM users WHERE username = ? ORDER BY id',
                   (name, ))
    result = cursor.fetchone()
    conn.close()

    if result:
        openid = result[0]
        return getFapiaoByOpenid(openid)
    else:
        return []  # 如果没有找到，返回空列表


def getFapiaoByStatus(openid):
    '''
    根据发票状态获取发票数据
    '''
    results = []
    if openid == admin_openid:
        conn = sqlite3.connect('./data/fapiao.db')
        cursor = conn.cursor()
        cursor.execute(
            'SELECT data, user,time FROM fapiao WHERE checked = 0 ORDER BY id DESC'
        )
        results = cursor.fetchall()
        conn.close()
    # 提取唯一的openid列表
    openid_list = list(set(result[1] for result in results))

    # 查询对应的username
    username_map = {}
    if openid_list:
        placeholders = ','.join(['?'] * len(openid_list))
        query = f'SELECT openid, username FROM users WHERE openid IN ({placeholders})'

        conn = sqlite3.connect('./data/users.db')
        cursor = conn.cursor()
        cursor.execute(query, openid_list)

        # 构建 {openid: username} 映射
        username_map = {
            openid: username
            for openid, username in cursor.fetchall()
        }
        conn.close()

    # 替换openid为username，处理可能缺失的映射情况
    updated_results = []
    for data, openid, time in results:
        username = username_map.get(openid,
                                    "未知用户")  # 如果找不到对应username，默认使用"未知用户"
        updated_results.append((data, username, time))
    return updated_results


def getAllowedByopenid(openid):
    '''
    根据openid获取允许的openid
    '''
    conn = sqlite3.connect('./data/users.db')
    cursor = conn.cursor()
    cursor.execute('SELECT allowed FROM users WHERE openid = ?', (openid, ))
    result = cursor.fetchone()
    conn.close()
    if result:
        return result[0]
    else:
        return None


def getFapiaoWithOthers():
    '''
    获取数据库中others列不为空的所有数据
    '''
    conn = sqlite3.connect('./data/fapiao.db')
    cursor = conn.cursor()
    cursor.execute(
        'SELECT data, time, checked, others, otherstime FROM fapiao WHERE others IS NOT NULL AND others != "[]" ORDER BY otherstime DESC'
    )
    results = cursor.fetchall()
    conn.close()

    if not results:
        return []
    results = list(results)

    # 自定义排序key函数
    def get_max_timestamp(item):
        # 提取最后一个元素（时间戳字符串列表）
        timestamps_str = item[-1]
        # 将字符串转换为实际的列表
        timestamps = eval(timestamps_str)
        # 返回列表中的最大值
        return max(timestamps)

    # 根据时间戳列表中的最大值进行降序排序
    results = sorted(results, key=get_max_timestamp, reverse=True)
    # 提取唯一的openid列表
    # 修改前: openid_list = list(set(result[3] for result in results))
    # 修改后: 合并所有result[3]中的数组并去重
    openid_list = []
    for result in results:
        try:
            others = eval(result[3])
            if isinstance(others, list):
                openid_list.extend(others)
            openid_list = list(set(openid_list))
        except Exception as e:
            print(f"Error parsing others: {e}")

    # 查询对应的username
    username_map = {}
    if openid_list:
        placeholders = ','.join(['?'] * len(openid_list))
        query = f'SELECT openid, username FROM users WHERE openid IN ({placeholders})'

        conn = sqlite3.connect('./data/users.db')
        cursor = conn.cursor()
        cursor.execute(query, openid_list)

        # 构建 {openid: username} 映射
        username_map = {
            openid: username
            for openid, username in cursor.fetchall()
        }
        conn.close()

    # 替换openid为username，处理可能缺失的映射情况
    updated_results = []
    for data, time, checked, others, otherstime in results:
        try:
            others = eval(others)
            # print(type(others))1
            if len(others) > 1:
                # username = username_map.get(openid,
                #                             "未知用户")  # 如果找不到对应username，默认使用"未知用户"
                for index, other in enumerate(others):
                    others[index] = username_map.get(other, "未知用户")
                updated_results.append(
                    (data, time, checked, others, otherstime))
        except Exception as e:
            print(f"Error parsing others: {e}")
            continue

    return updated_results


def getOpenidByFapiaoData(data):
    '''
    由发票内容获取上传人
    '''
    conn = sqlite3.connect('./data/fapiao.db')
    cursor = conn.cursor()
    cursor.execute("SELECT user,time FROM fapiao WHERE data=?", (data, ))
    row = cursor.fetchone()
    if row:
        return row
    else:
        return None


def check_fapiao_Expired(openid="", days=15):
    '''
    检查发票是否超过指定天数未报销
    '''
    conn = sqlite3.connect('./data/fapiao.db')
    cursor = conn.cursor()
    cursor.execute(
        "SELECT user,data,time,information FROM fapiao WHERE maketime < ? AND checked = 0 AND user LIKE ? ORDER BY id DESC",
        (
            time.strftime("%Y%m%d",
                          time.localtime(time.time() - days * 86400)),
            f"%{openid}%",
        ))
    rows = cursor.fetchall()
    # for row in rows:
    #     print(row)
    conn.close()
    return rows


# 使用示例
if __name__ == "__main__":
    '''
    
    '''
    # print(getFapiaoWithOthers())
    data = '''代码：\n号码：24322000000214062103\n金额：868.00\n日期：20240620\n校验码：'''
    # print(getFapiaoInfoByData(data))
    # save()
    # updateFapiaoDetail
    uptime = "2025-01-22"
    value = "1000.00"
    wechatsubscribe.send_notification(
        openid="oPQDE5KA-SXhlayCSSaH6VoB31Pk",
        template_name="审批",
        values=(
            "已审批",
            uptime,
            value,
            time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),
            "通过",
        ),
        page=f"pages/tools/tools?query={uptime}@{data}")
